From 183d824f881e54688ecd5bf7185446aa6a531ca2 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 13 Aug 2026 22:12:47 +0000 Subject: [PATCH] Enforce, or not, COMM_IDS.Studied on COMM_IDS --- db/schemas/lib/triggers/create/comm_ids.m4 | 197 ++++++++++++++++++++- 1 file changed, 195 insertions(+), 2 deletions(-) diff --git a/db/schemas/lib/triggers/create/comm_ids.m4 b/db/schemas/lib/triggers/create/comm_ids.m4 index 5a9c239..6092d13 100644 --- a/db/schemas/lib/triggers/create/comm_ids.m4 +++ b/db/schemas/lib/triggers/create/comm_ids.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl Copyright (C) 2023, 2026 The Meme Factory, Inc. http://www.karlpinc.com/ dnl dnl This program is free software: you can redistribute it and/or modify dnl it under the terms of the GNU Affero General Public License as published by @@ -31,13 +31,206 @@ CREATE OR REPLACE FUNCTION comm_ids_func () BEGIN -- Function for comm_ids insert and update triggers -- - -- AGPL_notice(` --', `2023', + -- AGPL_notice(` --', `2023, 2026', `The Meme Factory, Inc., www.karlpinc.com') IF TG_OP = 'UPDATE' THEN restrict_special_row(`COMM_IDS', `CommID', sdb_unknown_comm) END IF; + IF TG_OP = 'UPDATE' + AND NEW.studied <> OLD.studied + AND NEW.studied THEN + -- Prevent the use of the studied community for individuals + -- not born into the community and not under study. + + DECLARE + a_animid biography_data.animid%TYPE; + a_birthcomm biography_data.birthcomm%TYPE; + a_birthdate biography_data.birthdate%TYPE; + a_entrydate biography_data.entrydate%TYPE; + + BEGIN + -- COMM_MEMBS + DECLARE + a_commmid comm_membs.commmid%TYPE; + a_startdate comm_membs.startdate%TYPE; + a_enddate comm_membs.enddate%TYPE; + + BEGIN + -- Can't be in a studied community before the EntryDate unless + -- born into the community + SELECT comm_membs.commmid, comm_membs.animid + , comm_membs.startdate, comm_membs.enddate + , biography_data.birthcomm, biography_data.entrydate + , biography_data.birthdate + INTO a_commmid , a_animid + , a_startdate , a_enddate + , a_birthcomm , a_entrydate + , a_birthdate + FROM comm_membs + JOIN biography_data + ON (biography_data.animid = comm_membs.animid) + WHERE comm_membs.commid = NEW.commid + AND comm_membs.enddate < biography_data.entrydate + AND biography_data.birthcomm IS DISTINCT FROM NEW.commid + ORDER BY comm_membs.enddate, biography_data.animid; -- consistency + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of COMM_IDS' + , DETAIL = 'An individual cannot be observed -- have a row' + || ' in COMM_MEMBS --, in a studied' + || ' community, before they are studied unless' + || ' born into the community' + || ': Key (CommID) = (' + || NEW.commid + || '), Value (Studied) = (' + || NEW.studied + || '), Value (name) = (' + || NEW.name + || ': Key (BIOGRAPHY_DATA.AnimID) = (' + || NEW.animid + || '), Value (BIOGRAPHY_DATA.EntryDate) = (' + || a_entrydate + || '), Value (BIOGRAPHY_DATA.BirthDate) = (' + || a_birthdate + || '), Value (BIOGRAPHY_DATA.BirthComm) = (' + || textualize(`a_birthcomm') + || ': Key (COMM_MEMBS.CommMID) = (' + || a_commmid + || '), Value (COMM_MEMBS.CommID) = (' + || NEW.commid + || '), Value (COMM_MEMBS.AnimID) = (' + || a_animid + || '), Value (COMM_MEMBS.StartDate) = (' + || a_startdate + || '), Value (COMM_MEMBS.EndDate) = (' + || a_enddate + || ')'; + END IF; + END; + + -- WATCHES + DECLARE + a_wid watches.wid%TYPE; + a_date watches.date%TYPE; + a_type watches.type%TYPE; + + BEGIN + -- Can't be in a studied community before the EntryDate unless + -- born into the community + SELECT watches.wid, watches.animid, watches.date + , biography_data.birthcomm, biography_data.entrydate + , biography_data.birthdate + INTO a_wid , a_animid , a_date + , a_birthcomm , a_entrydate + , a_birthdate + FROM watches + JOIN biography_data + ON (biography_data.animid = watches.animid) + WHERE watches.commid = NEW.commid + AND watches.date < biography_data.entrydate + AND biography_data.birthcomm IS DISTINCT FROM NEW.commid + ORDER BY watches.date, biography_data.animid; -- consistency + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of COMM_IDS' + , DETAIL = 'An individual cannot be observed' + || ' -- have a row in WATCHES --, in a studied' + || ' community, before they are studied unless' + || ' born into the community' + || ': Key (CommID) = (' + || NEW.commid + || '), Value (Studied) = (' + || NEW.studied + || '), Value (name) = (' + || NEW.name + || ': Key (BIOGRAPHY_DATA.AnimID) = (' + || NEW.animid + || '), Value (BIOGRAPHY_DATA.EntryDate) = (' + || a_entrydate + || '), Value (BIOGRAPHY_DATA.BirthDate) = (' + || a_birthdate + || '), Value (BIOGRAPHY_DATA.BirthComm) = (' + || textualize(`a_birthcomm') + || ': Key (WATCHES.WID) = (' + || a_wid + || '), Value (WATCHES.AnimID) = (' + || a_animid + || '), Value (WATCHES.CommID) = (' + || NEW.commid + || '), Value (WATCHES.Date) = (' + || a_date + || ')'; + END IF; + END; + + -- NON_BREC_SIGHTING_SOURCES + DECLARE + a_id non_brec_sighting_sources.id%TYPE; + a_source non_brec_sighting_sources.source%TYPE; + a_date non_brec_sighting_sources.date%TYPE; + + BEGIN + -- Can't be in a studied community before the EntryDate unless + -- born into the community + SELECT non_brec_sighting_sources.id, non_brec_sighting_sources.source + , non_brec_sighting_sources.animid, non_brec_sighting_sources.date + , biography_data.birthcomm, biography_data.entrydate + , biography_data.birthdate + INTO a_id , a_source + , a_animid , a_date + , a_birthcomm , a_entrydate + , a_birthdate + FROM non_brec_sighting_sources + JOIN biography_data + ON (biography_data.animid = non_brec_sighting_sources.animid) + WHERE non_brec_sighting_sources.commid = NEW.commid + AND non_brec_sighting_sources.date < biography_data.entrydate + AND biography_data.birthcomm IS DISTINCT FROM NEW.commid + ORDER BY non_brec_sighting_sources.date -- consistency + , biography_data.animid; + + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of COMM_IDS' + , DETAIL = 'An individual cannot be observed -- have a row in' + || ' NON_BREC_SIGHTING_SOURCES --, in a studied' + || ' community, before they are studied unless' + || ' born into the community' + || ': Key (CommID) = (' + || NEW.commid + || '), Value (Studied) = (' + || NEW.studied + || '), Value (name) = (' + || NEW.name + || ': Key (BIOGRAPHY_DATA.AnimID) = (' + || NEW.animid + || '), Value (BIOGRAPHY_DATA.EntryDate) = (' + || a_entrydate + || '), Value (BIOGRAPHY_DATA.BirthDate) = (' + || a_birthdate + || '), Value (BIOGRAPHY_DATA.BirthComm) = (' + || textualize(`a_birthcomm') + || ': Key (NON_BREC_SIGHTING_SOURCES.ID) = (' + || a_id + || '), Value (NON_BREC_SIGHTING_SOURCES.Source) = (' + || a_source + || '), Value (NON_BREC_SIGHTING_SOURCES.Date) = (' + || a_date + || '), Value (NON_BREC_SIGHTING_SOURCES.AnimID) = (' + || a_animid + || '), Value (NON_BREC_SIGHTING_SOURCES.CommID) = (' + || NEW.commid + || ')'; + END IF; + END; + + END; + END IF; + RETURN NULL; END; $$; -- 2.34.1